using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Data;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// 患者退费密码仓储实现
    /// </summary>
    public class PatientRefundPasswordRepository : IPatientRefundPasswordRepository
    {
        private readonly IOracleConnectionFactory _connectionFactory;
        private readonly ILogger<PatientRefundPasswordRepository> _logger;

        public PatientRefundPasswordRepository(
            IOracleConnectionFactory connectionFactory,
            ILogger<PatientRefundPasswordRepository> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 更新患者退费密码相关信息
        /// </summary>
        /// <param name="request">更新请求</param>
        /// <returns>更新时间，如果更新失败则返回null</returns>
        public async Task<DateTime?> UpdatePatientRefundPasswordAsync(PatientRefundPasswordUpdateRequest request)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                connection.Open();

                // 先更新数据
                var updateSql = @"
                    UPDATE pca.pca_patient_info 
                    SET refund_password = :RefundPassword,
                        modifier = :Modifier,
                        modifier_card_type = :ModifierCardType,
                        modifier_card_code = :ModifierCardCode,
                        modify_time = :ModifyTime
                    WHERE patient_id = :PatientId";

                var updateTime = request.ModifyTime ?? DateTime.Now;
                var parameters = new
                {
                    RefundPassword = request.RefundPassword,
                    Modifier = request.Modifier,
                    ModifierCardType = request.ModifierCardType,
                    ModifierCardCode = request.ModifierCardCode,
                    ModifyTime = request.ModifyTime ?? DateTime.Now,
                    PatientId = request.PatientId
                };

                var rowsAffected = await connection.ExecuteAsync(updateSql, parameters);
                
                if (rowsAffected > 0)
                {
                    // 插入密码修改日志
                    var insertLogSql = @"
                        INSERT INTO pca.pca_refund_password_log 
                        (patient_id, refund_password, modifier, modifier_card_type, modifier_card_code, modify_time, org_password)
                        VALUES 
                        (:PatientId, :RefundPassword, :Modifier, :ModifierCardType, :ModifierCardCode, :ModifyTime, :OrgPassword)";

                    var logParameters = new
                    {
                        PatientId = request.PatientId,
                        RefundPassword = request.RefundPassword,
                        Modifier = request.Modifier,
                        ModifierCardType = request.ModifierCardType,
                        ModifierCardCode = request.ModifierCardCode,
                        ModifyTime = updateTime,
                        OrgPassword = request.OrgPassword
                    };

                    await connection.ExecuteAsync(insertLogSql, logParameters);

                    // 获取更新后的modify_time
                    var selectSql = "SELECT modify_time FROM pca.pca_patient_info WHERE patient_id = :PatientId";
                    var modifyTime = await connection.QuerySingleOrDefaultAsync<DateTime?>(selectSql, new { PatientId = request.PatientId });
                    
                    _logger.LogInformation("Updated patient refund password for PatientId: {PatientId}, ModifyTime: {ModifyTime}, logged to pca_refund_password_log", 
                        request.PatientId, modifyTime);
                    
                    return modifyTime;
                }
                
                _logger.LogWarning("No rows affected when updating patient refund password for PatientId: {PatientId}", request.PatientId);
                return null;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating patient refund password for PatientId: {PatientId}", request.PatientId);
                throw;
            }
        }

        /// <summary>
        /// 检查患者是否存在
        /// </summary>
        /// <param name="patientId">患者ID</param>
        /// <returns>是否存在</returns>
        public async Task<bool> PatientExistsAsync(string patientId)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                connection.Open();

                var sql = "SELECT COUNT(1) FROM pca.pca_patient_info WHERE patient_id = :PatientId";
                var count = await connection.QuerySingleAsync<int>(sql, new { PatientId = patientId });
                
                return count > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error checking if patient exists for PatientId: {PatientId}", patientId);
                throw;
            }
        }

        /// <summary>
        /// 删除退费密码日志记录（用于回滚操作）
        /// </summary>
        /// <param name="patientId">患者ID</param>
        /// <param name="modifyTime">修改时间</param>
        /// <returns>是否删除成功</returns>
        public async Task<bool> DeleteRefundPasswordLogAsync(string patientId, DateTime modifyTime)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                connection.Open();

                // 删除指定患者和修改时间的日志记录
                // 使用时间范围匹配，允许1秒的误差
                var deleteSql = @"
                    DELETE FROM pca.pca_refund_password_log 
                    WHERE patient_id = :PatientId 
                    AND modify_time BETWEEN :StartTime AND :EndTime";

                var startTime = modifyTime.AddSeconds(-1);
                var endTime = modifyTime.AddSeconds(1);

                var parameters = new
                {
                    PatientId = patientId,
                    StartTime = startTime,
                    EndTime = endTime
                };

                var rowsAffected = await connection.ExecuteAsync(deleteSql, parameters);
                
                _logger.LogInformation("Deleted {RowsAffected} refund password log records for PatientId: {PatientId}, ModifyTime: {ModifyTime}", 
                    rowsAffected, patientId, modifyTime);
                
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error deleting refund password log for PatientId: {PatientId}, ModifyTime: {ModifyTime}", 
                    patientId, modifyTime);
                throw;
            }
        }
    }
}